package com.huaxia.dao.newAdd;


import com.huaxia.pojo.newAdd.SaleDeptNewAdd;

import org.apache.ibatis.annotations.Select;


import java.util.List;

/**
 * @author wangli 爱我华夏，爱我中华！
 * @date 2020/06/05 17:26
 */

public interface SaleDeptNewAddMapper {

    @Select("select t1.SALEDEPTSNAME,nvl(t2.SALEDEPTNewAgent,0)SALEDEPTNewAgent,\n" +
            "       nvl(t3.SALEDEPTAllAgent,0)SALEDEPTAllAgent,\n" +
            "       nvl(t4.SALEDEPTChargeNum,0)SALEDEPTChargeNum,\n" +
            "       nvl(t5.SALEDEPTChargeIncrease,0)SALEDEPTChargeIncrease,\n" +
            "       round(decode(nvl(t4.SALEDEPTChargeNum,0),0,-9999,nvl(t5.SALEDEPTChargeIncrease,0)*100/nvl(t4.SALEDEPTChargeNum,0)),1) SALEDEPTChargeYield from\n" +
            "   SD_SALEDEPT t1 left join     \n" +
            "        (select t.SALEDEPTCODE,count(t.SALEDEPTCODE)saleDeptNewAgent from D_AGENT_PROVINCE_YX2 t where TO_CHAR(t.EMPLOYDATE,'YYYY/MM')=TO_CHAR(sysdate,'YYYY/MM') \n" +
            "            and t.AGENTSTATE='在职' and t.name <> '客户服务专员' group by t.SALEDEPTCODE\n" +
            "        )t2 on t1.SALEDEPTCODE=t2.SALEDEPTCODE left join\n" +
            "        (select t.SALEDEPTCODE,count(t.SALEDEPTCODE)saleDeptAllAgent from D_AGENT_PROVINCE_YX2 t where  t.AGENTSTATE='在职' and t.name <> '客户服务专员' group by t.SALEDEPTCODE\n" +
            "        )t3 on t1.SALEDEPTCODE=t3.SALEDEPTCODE left join\n" +
            "        ( select t.SALEDEPTCODE,count(t.BRANCHCODE)saleDeptChargeNum from D_AGENT_PROVINCE_YX2 t where\n" +
            "         t.AGENTSTATE='在职' and t.name <> '客户服务专员' and t.AGENTGRADE in('BC','SBC','BM','SBM','AS','SAS','ZG201','ZG202','ZG203','ZG301','ZG302','ZG401') group by t.SALEDEPTCODE\n" +
            "         )t4 on t1.SALEDEPTCODE=t4.SALEDEPTCODE left join\n" +
            "        (\n" +
            "select b.SALEDEPTCODE,count(b.SALEDEPTCODE)saleDeptChargeIncrease from(select t.RECOMMAGENTCODE from D_AGENT_PROVINCE_YX2 t where \n" +
            "            TO_CHAR(t.EMPLOYDATE,'YYYY/MM')=TO_CHAR(sysdate,'YYYY/MM')  AND  t.AGENTSTATE='在职'and t.name <> '客户服务专员' GROUP BY t.RECOMMAGENTCODE)a,\n" +
            "            (select t.SALEDEPTCODE,t.AGENTCODE  from D_AGENT_PROVINCE_YX2 t where\n" +
            "             t.AGENTSTATE='在职'  and t.name <> '客户服务专员' and t.AGENTGRADE in('BC','SBC','BM','SBM','AS','SAS','ZG201','ZG202','ZG203','ZG301','ZG302','ZG401') \n" +
            "             )b where b.AGENTCODE=a.RECOMMAGENTCODE group by b.SALEDEPTCODE\n" +
            "        )t5 on t1.SALEDEPTCODE=t5.SALEDEPTCODE order by t2.SALEDEPTNewAgent DESC nulls last")
    List<SaleDeptNewAdd> getSaleDeptNewAdd();
}
